package com.glsc.ngateway.common.base.repo.mysql.opmanagemain.archive;

import com.glsc.ngateway.common.base.domain.mysql.opmanagemain.archive.ArchiveClient;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Map;

@Repository
public interface ArchiveClientRepository extends JpaRepository<ArchiveClient, String>, JpaSpecificationExecutor<ArchiveClient> {

    @Query(value =
            "select " +
                    " a.id, a.party_name, a.party_organ_code,   " +
                    " b.cnt " +
                    "from " +
                    " t_counter_party a " +
                    "left join ( " +
                    " select " +
                    "  a.party_id , count(*) cnt " +
                    " from " +
                    "  t_archive_client a " +
                    " where " +
                    "  1=1 " +
                    " group by " +
                    "  a.party_id ) b on " +
                    " a.id = b.party_id " +
                    "where  " +
                    "a.enable_flag = 'Y'  " +
                    "and (:partyName IS NULL OR a.party_name LIKE CONCAT('%',:partyName,'%')  ) " +
                    "and (:partyOrganCode IS NULL OR a.party_organ_code LIKE CONCAT('%',:partyName,'%')  ) " +
                    "order by  " +
                    "b.cnt desc "
            , countQuery = "select " +
            " count(1) " +
            "from " +
            " t_counter_party a " +
            "left join ( " +
            " select " +
            "  a.party_id , count(*) cnt " +
            " from " +
            "  t_archive_client a " +
            " where " +
            " 1=1 " +
            " group by " +
            "  a.party_id ) b on " +
            " a.id = b.party_id " +
            "where  " +
            "a.enable_flag = 'Y'  " +
            "and (:partyName IS NULL OR a.party_name LIKE CONCAT('%',:partyName,'%')  ) " +
            "and (:partyOrganCode IS NULL OR a.party_organ_code LIKE CONCAT('%',:partyName,'%')  ) " +
            "order by  " +
            "b.cnt desc",
            nativeQuery = true)
    Page<Map<String,Object>> findOrgClientPage(@Param("partyName") String partyName, @Param("partyOrganCode") String partyOrganCode
            , Pageable pageable);
}
